import { BasedbService } from './basedb';

type FileTagMapRecord = {
  filename: string;
  tags?: [];
  thumbnail?: Buffer;
  originBody?: string;
  path?: string;
};

class FiledbService extends BasedbService {
  tableName: string;
  constructor() {
    super({
      dbname: 'sqlite-file.db'
    });
    this.tableName = 'file';
    this.initTable();
  }

  /*
   * 初始化表
   */
  private initTable(): void {
    // 检查表是否存在
    const masterStmt = this.db.prepare(
      'SELECT * FROM sqlite_master WHERE type=? AND name = ?'
    );
    let tableExists = masterStmt.get('table', this.tableName);
    if (!tableExists) {
      // 创建表
      const create_table_sql = `CREATE TABLE ${this.tableName}
        (
           id INTEGER PRIMARY KEY AUTOINCREMENT,
           filename CHAR(50) NOT NULL,
           tags CHAR(200) NOT NULL,
           thumbnail CHAR(50) NOT NULL,
           originBody CHAR(200) NOT NULL
        );`;
      this.db.exec(create_table_sql);
    }
  }

  /*
   * 保存文件路径映射关系 (sqlite)
   */
  addFile(data: FileTagMapRecord) {
    const insert = this.db.prepare(
      `INSERT INTO ${this.tableName} (filename,tags,thumbnail,originBody) 
      VALUES (@filename,@tags,@thumbnail,@originBody)`
    );
    insert.run(data);
    return true;
  }

  /*
   * 查询file表所有数据 (sqlite)
   */
  getAllFile() {
    const selectAllUser = this.db.prepare(`SELECT * FROM ${this.tableName} `);
    return selectAllUser.all();
  }

  /*
   * 查询file表中指定条件的数据 (sqlite)
   */
  getFile(data: FileTagMapRecord) {
    const tags = data.tags?.toString();
    const selectFile = this.db.prepare(
      `SELECT * FROM ${this.tableName} WHERE filename LIKE @filename OR tags LIKE @tags`
    );
    const files = selectFile.all({
      filename: `%${data.filename}%`,
      tags: `%${tags}%`
    });
    return files;
  }

  /*
   * 删除file表中指定条件的数据 (sqlite)
   */
  deleteFile(id: number) {
    const delUser = this.db.prepare(
      `DELETE FROM ${this.tableName} WHERE id = ?`
    );
    delUser.run(id);
    return true;
  }

  /*
   * get data dir (sqlite)
   */
  getDataDir() {
    const dir = this.storage.getDbDir();
    return dir;
  }
}

FiledbService.toString = () => '[class FiledbService]';
const filedbService = new FiledbService();

export { FiledbService, filedbService };
